package core;

import java.math.BigDecimal;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONObject;

import core.db.DBConnectionUtil;
import core.util.Const;
import core.util.CoreDataUtil;
import core.util.SqlData;
@SuppressWarnings("rawtypes")
public abstract class CoreDao {
	protected abstract List<Map<String, Object>> invoke(Connection con, String methodKey) throws Exception;
	protected abstract List<Map<String, Object>> invoke(Connection con, String methodKey, Object... param)throws Exception;
	protected abstract List<Map<String, Object>> invokeSql(Connection con, String sql, Object... paramMap)throws Exception;
	protected abstract List<JSONObject> invokeJson(Connection con, String methodKey, Object... param) throws Exception;
	protected abstract List<JSONObject> invokeJsonSql(Connection con, String sql, Object... paramMap) throws Exception;
	
	/**
	 * 数据库维护语句
	 * 
	 * @param con
	 * @throws SQLException
	 */
	public int ddl(Connection con, String sql) throws Exception {
		if (getSqlType(sql, Const.SELECT) || getSqlType(sql, Const.DELETE) || getSqlType(sql, Const.UPDATE)
				|| getSqlType(sql, Const.SELECT)) {
			throw new Exception("ddl只用来执行DDL语句");
		}
		PreparedStatement ps = con.prepareStatement(sql);
		int b = ps.executeUpdate();
		return b;
	}

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> servcie(Connection con, String methodKey, Object param) throws Exception {
		if (param instanceof List) {
			return invoke(con, methodKey, (List) param);
		} else if (param instanceof Object[]) {
			return invoke(con, methodKey, param);
		} else {
			return invoke(con, methodKey);
		}
	}

	public List<JSONObject> serviceJson(Connection con, String methodKey, Object param) throws Exception {
		return invokeJson(con, methodKey, param);
	}

	public List<Map<String, Object>> servcieSql(Connection con, String sql, Object param) throws Exception {
		return invokeSql(con, sql, param);
	}

	public List<JSONObject> serviceJsonSql(Connection con, String sql, Object param) throws Exception {
		return invokeJsonSql(con, sql, param);
	}

	protected List<Map<String, Object>> getResultList(Connection con, String methodKey, Object params) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		try {
			String sql = ((SqlData) CoreDataUtil.dataMap.get(methodKey)).getSql();
			if (getSqlType(sql, Const.SELECT)) {
				ps = con.prepareStatement(sql);
			} else {
				ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			}
			setParams(params, ps);
			executeSql(sql, resultList, ps, rs);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBConnectionUtil.close(rs, ps, null);
		}
		return resultList;
	}

	protected List<Map<String, Object>> getResultListSql(Connection con, String sql, Object params) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>();
		try {
			if (getSqlType(sql, Const.SELECT)) {
				ps = con.prepareStatement(sql);
			} else {
				ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			}
			setParams(params, ps);
			executeSql(sql, resultList, ps, rs);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBConnectionUtil.close(rs, ps, null);
		}
		return resultList;
	}

	protected List<JSONObject> getResultJsons(Connection con, String methodKey, Object param) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<JSONObject> resultList = new ArrayList<JSONObject>();
		try {
			String sql = ((SqlData) CoreDataUtil.dataMap.get(methodKey)).getSql();
			if (getSqlType(sql, Const.SELECT)) {
				ps = con.prepareStatement(sql);
			} else {
				ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			}
			setParams(param, ps);
			executeJson(sql, resultList, ps, rs);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBConnectionUtil.close(rs, ps, null);
		}
		return resultList;
	}

	protected List<JSONObject> getResultJsonsSql(Connection con, String sql, Object param) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<JSONObject> resultList = new ArrayList<JSONObject>();
		try {
			if (getSqlType(sql, Const.SELECT)) {
				ps = con.prepareStatement(sql);
			} else {
				ps = con.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			}
			setParams(param, ps);
			executeJson(sql, resultList, ps, rs);
		} catch (SQLException e) {
			throw e;
		} finally {
			DBConnectionUtil.close(rs, ps, null);
		}
		return resultList;
	}

	protected void setPrepareStatementBody(Object obj, int i, PreparedStatement ps) throws SQLException {
		if (obj instanceof java.lang.String) {
			ps.setString(i, (String) obj);
		} else if (obj instanceof java.lang.Integer) {
			ps.setInt(i, (Integer) obj);
		} else if (obj instanceof java.lang.Double) {
			ps.setDouble(i, (Double) obj);
		} else if (obj instanceof java.util.Date) {
			ps.setDate(i, new java.sql.Date(((java.util.Date) obj).getTime()));
		} else if (obj instanceof Timestamp) {
			ps.setTimestamp(i, (Timestamp) obj);
		} else if (obj instanceof Blob) {
			ps.setBlob(i, (Blob) obj);
		} else if (obj instanceof BigDecimal) {
			ps.setBigDecimal(i, (BigDecimal) obj);
		} else if (obj instanceof Long) {
			ps.setLong(i, (Long) obj);
		}
	}

	protected void setList(Object params, PreparedStatement ps) throws SQLException {
		for (int i = 0; i < ((List) params).size(); i++) {
			Object obj = ((List) params).get(i);
			setPrepareStatementBody(obj, i + 1, ps);
		}
	}

	protected void setObjArray(Object params, PreparedStatement ps) throws SQLException {
		for (int i = 0; i < ((Object[]) params).length; i++) {
			setPrepareStatementBody(((Object[]) params)[i], i + 1, ps);
		}
	}

	protected void setParams(Object params, PreparedStatement ps) throws SQLException {
		if (params != null) {
			if (params instanceof List) {
				setList(params, ps);
			} else if (params instanceof Object[]) {
				Object obj = ((Object[]) params)[0];
				setObjArray(obj, ps);
			}
		}
	}

	protected void executeJson(String sqlStatus, List<JSONObject> resultList, PreparedStatement ps, ResultSet rs)
			throws Exception {
		if (getSqlType(sqlStatus, Const.INSERT)) {
			int id = ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			if(rs.next())
				id = rs.getInt(1);
			JSONObject obj = new JSONObject();
			obj.put("result", id);
			resultList.add(obj);
		} else if (getSqlType(sqlStatus, Const.DELETE)) {
			int id = ps.executeUpdate();
			JSONObject obj = new JSONObject();
			obj.put("result", id);
			resultList.add(obj);
		} else if (getSqlType(sqlStatus, Const.UPDATE)) {
			ps.executeUpdate();
			int cnt = ps.getUpdateCount();
			JSONObject obj = new JSONObject();
			obj.put("result", cnt);
			resultList.add(obj);
		} else if (getSqlType(sqlStatus, Const.SELECT)) {
			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columCnt = metaData.getColumnCount();
			while (rs.next()) {
				JSONObject obj = new JSONObject();
				for (int i = 0; i < columCnt; i++) {
					String columName = metaData.getColumnLabel(i + 1);
					Object columValue = rs.getObject(i + 1);
					obj.put(columName, columValue == null ? "" : columValue);
				}
				resultList.add(obj);
			}
		}
	}

	protected void executeSql(String sqlStatus, List<Map<String, Object>> resultList, PreparedStatement ps, ResultSet rs)
			throws SQLException {
		if (getSqlType(sqlStatus, Const.INSERT)) {
			int id = ps.executeUpdate();
			rs = ps.getGeneratedKeys();
			rs.next();
			id = rs.getInt(1);
			Map<String, Object> columMap = new HashMap<String, Object>();
			columMap.put("result", id);
			resultList.add(columMap);
		} else if (getSqlType(sqlStatus, Const.DELETE)) {
			int id = ps.executeUpdate();
			Map<String, Object> columMap = new HashMap<String, Object>();
			columMap.put("result", id);
			resultList.add(columMap);
		} else if (getSqlType(sqlStatus, Const.UPDATE)) {
			ps.executeUpdate();
			int cnt = ps.getUpdateCount();
			Map<String, Object> columMap = new HashMap<String, Object>();
			columMap.put("result", cnt);
			resultList.add(columMap);
		} else if (getSqlType(sqlStatus, Const.SELECT)) {
			rs = ps.executeQuery();
			ResultSetMetaData metaData = rs.getMetaData();
			int columCnt = metaData.getColumnCount();
			while (rs.next()) {
				Map<String, Object> columMap = new HashMap<String, Object>();
				for (int i = 0; i < columCnt; i++) {
					String columName = metaData.getColumnName(i + 1);
					Object columValue = rs.getObject(i + 1);
					columMap.put(columName, columValue);
				}
				resultList.add(columMap);
			}
		}
	}

	protected boolean getSqlType(String sql, String type) {
		String jugSQL = sql.toUpperCase().replaceAll(" ", "").replaceAll("\r\n", "").replaceAll("\n\r", "")
				.replaceAll("\r", "").replaceAll("\n", "").replaceAll("\t", "");
		type = type.trim();
		if (type.equalsIgnoreCase(jugSQL.substring(0, type.length()))) {
			return true;
		}
		return false;
	}

	public <T> List<T> covertToBean(List<JSONObject> objs, Class clazz) {
		List<T> list = new ArrayList<T>();
		for (int i = 0; i < objs.size(); i++) {
			JSONObject json = objs.get(i);
			T bean = (T) JSONObject.toBean(json, clazz);
			list.add(bean);
		}
		return list;
	}
}
